package com.polytech.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

public class Database {

	private Connection db;
	private DataSource dataSource;
	
	public Database(DataSource dataSource){
			setDataSource(dataSource);
	}
	public DataSource getDataSource() {
		return dataSource;
	}
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
		try {
			db=dataSource.getConnexion();
			System.out.println("connexion reussie");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public Object [][] select(String tableName,Object [][]creteres){
		return select(tableName, creteres,null);
	}
	public Object [][] select(String tableName,Object [][]creteres,String []fields){
		String req="SELECT ";
		if(fields!=null){
			req+=fields[0];
			for(int i=1;i<fields.length;i++){
				req+=","+fields[i];
			}
		}else{
			req+=" * ";
		}
		req+=" FROM "+tableName;
		if(creteres!=null){
			req+=" WHERE ";
			req+=creteres[0][0]+"="+toStringSqlValue(creteres[0][1]);
			for (int i = 1; i < creteres.length; i++) {
				req+=" AND "+creteres[i][0]+"="+toStringSqlValue(creteres[i][1]);
			}
		}
		return executeRequete(req);
	}
	public Object [][] select(String tableName) {
		 return select(tableName,null,null);
	}
	public Object [][] select(String tableName,String []fields) {
		 return select(tableName,null,fields);
	}

	public int insert(String tableName , Object rows[]) {
		StringBuffer req= new StringBuffer("INSERT INTO "+ tableName +" VALUES (");
		for (int i = 0; i < rows.length-1; i++) {
			req.append(toStringSqlValue(rows[i])+",");
		}
		req.append(toStringSqlValue(rows[rows.length-1])+")");
		System.out.println(req.toString());
		try {
			Statement sqle=db.createStatement();
			return sqle.executeUpdate(req.toString());
	      }
		catch (Exception e) {e.printStackTrace();}
		return 0;
	}
	
	public int delete(String tableName,String criteria){
		String req="DELETE FROM "+tableName+" WHERE"+criteria;
		try {
			Statement sql=db.createStatement();
			return sql.executeUpdate(req);
		} catch (Exception e) {
			System.out.println("Erreur: "+e.getMessage());
			return 0;
		}
	}
	
	public int delete(String tableName,String key,Object value){
			return delete(tableName, key+"="+toStringSqlValue(value));
	}
	public Object [][] executeRequete(String req){
		System.out.println("Req>>>"+req);
		Object rows[][] = null;
		try {
			Statement statement=db.createStatement();
			ResultSet rs = statement.executeQuery(req);
			ResultSetMetaData rsm=rs.getMetaData();
			int Nc=rsm.getColumnCount();
			rs.last();
			int Nl=rs.getRow();
			rs.beforeFirst();
			if(Nl==0)
				return null;
			rows = new Object [Nl][Nc];
			int L=0;
			while (rs.next()) {
				for (int i = 1; i <= Nc; i++) {
					switch (rsm.getColumnType(i)) {
					case Types.DATE:
						 rows[L][i-1] = rs.getDate(i);
						break;
					case Types.BOOLEAN:
						rows[L][i-1] = rs.getBoolean(i);
						break;
					case Types.INTEGER:
						 rows[L][i-1] = rs.getInt(i);
						break;	
					default:
						rows[L][i-1] = rs.getString(i);
						break;
					}
				}
				L++;				
			}
		} catch (SQLException e) {e.printStackTrace();}
		return rows;
	}
	public ResultSet executeQuery(String query){
		Statement state;
		try {
			state = db.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			return state.executeQuery(query);
		} catch (SQLException e) {e.printStackTrace();}
		return null;
		
	}
	public int excuteUpdate(String req){
		try {
			Statement stat=db.createStatement();
			return stat.executeUpdate(req);
		} catch (SQLException e) {e.printStackTrace();}
		return 0;
		
	}
	private String toStringSqlValue(Object o){
		if(o instanceof String){
			String s=(String) o;
			s=s.replace("'","\\'");
			return "'"+s+"'";
		}
		if(o instanceof Date){
			DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
			return "'"+dateFormat.format(o)+"'";
		}
		return ""+o;
	}
}